package com.lzwk.app.ods.adb;

import com.lzwk.common.adb.ADBConstant;
import org.apache.flink.table.api.TableResult;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;

/**
 * @title: AdbJDBCTables
 * @Author Yeaser
 * @Date: 2022/2/18 17:42
 * @Version 1.0
 * Description: TODO
 */
public class AdbJDBCTables {


    public static void adbDivisionTermClass(StreamTableEnvironment tenv) {
        tenv.executeSql(" CREATE TABLE adb_division_term_class ( " +
                "  `id` INT, " +
                "  `division_activity_id` INT, " +
                "  `activity_term_id` INT, " +
                "  `channel_category` STRING, " +
                "  `class_name` STRING, " +
                "  `rank` INT, " +
                "  `object_id` INT, " +
                "  `object_type` STRING, " +
                "  `student_number` INT, " +
                "  `can_redirect` INT, " +
                "  `create_time` TIMESTAMP(0), " +
                "  `update_time` TIMESTAMP(0), " +
                "  `state` STRING, " +
                "  `monitor_id` INT, " +
                "  `monitor_name` STRING, " +
                "  PRIMARY KEY (`id`) NOT ENFORCED" +
                ") with ( " +
                "  'connector' = 'jdbc', " +
                "  'driver' = 'com.mysql.cj.jdbc.Driver', " +
                "  'url'='jdbc:mysql://" + ADBConstant.ADBSLAVEHOST + ":" + ADBConstant.ADBSLAVEPORT + "/" + ADBConstant.ADBSLAVEDBNAME + "?characterEncoding=utf8&serverTimezone=Asia/Shanghai&autoReconnect=true&tinyInt1isBit=false&transformedBitIsBoolean=false', " +
                "  'table-name' = " + "'" + ADBConstant.ADBJDBCDIVISIONTERMCLASS + "'" + ", " +
                "  'username'=" + "'" + ADBConstant.ADBSLAVEUSERNAME + "'" + ", " +
                "  'password'=" + "'" + ADBConstant.ADBSLAVEPASSWORD + "'" + ", " +
                "  'lookup.cache.max-rows'='2000', " +
                "  'lookup.cache.ttl'='600000' " +
                ")");

    }


    public static void adbClassStudentArchive(StreamTableEnvironment tenv) {
        tenv.executeSql(" CREATE TABLE adb_division_class_student_archive ( " +
                " `account_id` INT, " +
                " `term_class_id` INT, " +
                " `id` INT, " +
                " `division_activity_id` INT, " +
                " `activity_term_id` INT, " +
                " `state` STRING, " +
                " `refund_status` INT, " +
                " `remark` STRING, " +
                " `create_time` TIMESTAMP(0), " +
                " `update_time` TIMESTAMP(0), " +
                "  PRIMARY KEY (`id`) NOT ENFORCED" +
                ") with ( " +
                "  'connector' = 'jdbc', " +
                "  'driver' = 'com.mysql.cj.jdbc.Driver', " +
                "  'url'='jdbc:mysql://" + ADBConstant.ADBSLAVEHOST + ":" + ADBConstant.ADBSLAVEPORT + "/" + ADBConstant.ADBSLAVEDBNAME + "?characterEncoding=utf8&serverTimezone=Asia/Shanghai&autoReconnect=true&tinyInt1isBit=false&transformedBitIsBoolean=false', " +
                "  'table-name' = " + "'" + ADBConstant.ADBJDBCDIVISIONCLASSSTUDENTARCHIVE + "'" + ", " +
                "  'username'=" + "'" + ADBConstant.ADBSLAVEUSERNAME + "'" + ", " +
                "  'password'=" + "'" + ADBConstant.ADBSLAVEPASSWORD + "'" + ", " +
                "  'lookup.cache.max-rows'='2000', " +
                "  'lookup.cache.ttl'='600000' " +
                ")");
    }


    public static void adbaActivityTerm(StreamTableEnvironment tenv) {
        tenv.executeSql(" CREATE TABLE adb_division_activity_term ( " +
                "  `id` INT, " +
                "  `division_activity_id` INT, " +
                "  `parent_id` INT, " +
                "  `grade` INT, " +
                "  `content_sync_term` INT, " +
                "  `wrapper_sync_term` INT, " +
                "  `rank` INT, " +
                "  `template_id` INT, " +
                "  `template_type` STRING, " +
                "  `start_time` TIMESTAMP(0), " +
                "  `end_time` TIMESTAMP(0), " +
                "  `class_limit` INT, " +
                "  `class_student_limit` INT, " +
                "  `student_cnt` INT, " +
                "  `state` STRING, " +
                "  `create_time` TIMESTAMP(0), " +
                "  `update_time` TIMESTAMP(0), " +
                "  `data_end_time` TIMESTAMP(0), " +
                "  `promote_start_time` TIMESTAMP(0), " +
                "  `remark` STRING, " +
                "  PRIMARY KEY (`id`) NOT ENFORCED" +
                ") with ( " +
                "  'connector' = 'jdbc', " +
                "  'driver' = 'com.mysql.cj.jdbc.Driver', " +
                "  'url'='jdbc:mysql://" + ADBConstant.ADBSLAVEHOST + ":" + ADBConstant.ADBSLAVEPORT + "/" + ADBConstant.ADBSLAVEDBNAME + "?characterEncoding=utf8&serverTimezone=Asia/Shanghai&autoReconnect=true&tinyInt1isBit=false&transformedBitIsBoolean=false', " +
                "  'table-name' = " + "'" + ADBConstant.ADBJDBCDIVISIONACTIVITYTERM + "'" + ", " +
                "  'username'=" + "'" + ADBConstant.ADBSLAVEUSERNAME + "'" + ", " +
                "  'password'=" + "'" + ADBConstant.ADBSLAVEPASSWORD + "'" + ", " +
                "  'lookup.cache.max-rows'='2000', " +
                "  'lookup.cache.ttl'='600000' " +
                ")");

    }


    public static void adbAccountMapping(StreamTableEnvironment tenv) {
        tenv.executeSql(" CREATE TABLE adb_account_mapping ( " +
                "  `object_id` STRING, " +
                "  `object_type` STRING, " +
                "  `account_id` INT, " +
                "  `state` STRING, " +
                "  `create_time` TIMESTAMP(0), " +
                "  `update_time` TIMESTAMP(0), " +
                "  `id` INT, " +
                "  `remark` STRING, " +
                "  primary key (id) not enforced " +
                ") with ( " +
                "  'connector' = 'jdbc', " +
                "  'driver' = 'com.mysql.cj.jdbc.Driver', " +
                "  'url' = 'jdbc:mysql://" + ADBConstant.ADBSLAVEHOST + ":" + ADBConstant.ADBSLAVEPORT + "/" + ADBConstant.ADBSLAVEDBNAME + "?characterEncoding=utf8&serverTimezone=Asia/Shanghai&autoReconnect=true&tinyInt1isBit=false&transformedBitIsBoolean=false', " +
                "  'table-name' = " + "'" + ADBConstant.ADBJDBCDIVISIONACCOUNTMAPPING + "'" + ", " +
                "  'username'=" + "'" + ADBConstant.ADBSLAVEUSERNAME + "'" + ", " +
                "  'password'=" + "'" + ADBConstant.ADBSLAVEPASSWORD + "'" + ", " +
                "  'lookup.cache.max-rows'='10000', " +
                "  'lookup.cache.ttl'='600000' " +
                ")");

    }

    public static void adbDivisionActvity(StreamTableEnvironment tenv) {
        tenv.executeSql(" CREATE TABLE adb_division_activity ( " +
                "  `id` INT, " +
                "  `liveroom_id` INT, " +
                "  `parent_id` INT, " +
                "  `grade` INT, " +
                "  `top` INT, " +
                "  `activity_name` STRING, " +
                "  `activity_url` STRING, " +
                "  `description` STRING, " +
                "  `state` STRING, " +
                "  `create_time` TIMESTAMP(0), " +
                "  `update_time` TIMESTAMP(0), " +
                "  primary key (id) not enforced " +
                ") with ( " +
                "  'connector' = 'jdbc', " +
                "  'driver' = 'com.mysql.cj.jdbc.Driver', " +
                "  'url' = 'jdbc:mysql://" + ADBConstant.ADBSLAVEHOST + ":" + ADBConstant.ADBSLAVEPORT + "/" + ADBConstant.ADBSLAVEDBNAME + "?characterEncoding=utf8&serverTimezone=Asia/Shanghai&autoReconnect=true&tinyInt1isBit=false&transformedBitIsBoolean=false', " +
                "  'table-name' = " + "'" + ADBConstant.ADBJDBCDIVISIONACTIVITY + "'" + ", " +
                "  'username'=" + "'" + ADBConstant.ADBSLAVEUSERNAME + "'" + ", " +
                "  'password'=" + "'" + ADBConstant.ADBSLAVEPASSWORD + "'" + ", " +
                "  'lookup.cache.max-rows'='20', " +
                "  'lookup.cache.ttl'='600000' " +
                ")");

    }


    public static void adbClassStudet(StreamTableEnvironment tenv) {
        tenv.executeSql(" CREATE TABLE adb_class_student ( " +
                "  `id` INT, " +
                "  `division_activity_id` INT, " +
                "  `activity_term_id` INT, " +
                "  `term_class_id` INT, " +
                "  `account_id` INT, " +
                "  `state` STRING, " +
                "  `refund_status` INT, " +
                "  `remark` STRING, " +
                "  `create_time` TIMESTAMP(0), " +
                "  `update_time` TIMESTAMP(0), " +
                "  primary key (id) not enforced " +
                ") with ( " +
                "  'connector' = 'jdbc', " +
                "  'driver' = 'com.mysql.cj.jdbc.Driver', " +
                "  'url' = 'jdbc:mysql://" + ADBConstant.ADBSLAVEHOST + ":" + ADBConstant.ADBSLAVEPORT + "/" + ADBConstant.ADBSLAVEDBNAME + "?characterEncoding=utf8&serverTimezone=Asia/Shanghai&autoReconnect=true&tinyInt1isBit=false&transformedBitIsBoolean=false', " +
                "  'table-name' = " + "'" + ADBConstant.ADBJDBCDIVISIONCLASSSTUDENT + "'" + ", " +
                "  'username'=" + "'" + ADBConstant.ADBSLAVEUSERNAME + "'" + ", " +
                "  'password'=" + "'" + ADBConstant.ADBSLAVEPASSWORD + "'" + ", " +
                "  'lookup.cache.max-rows'='1000', " +
                "  'lookup.cache.ttl'='600000' " +
                ")");

    }
}
